﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.SQLite;
using System.Text.RegularExpressions;
using System.ComponentModel;

namespace PTCScheduler.EditMenu_Pages
{
    /// <summary>
    /// Interaction logic for UpdateConference_Page.xaml
    /// </summary>
    public partial class UpdateConference_Page : Page, ScheduleReport
    {
        //SQLite connection for the main window page
        private SQLiteConnection conn;

        //SQLite data dapter for main window page
        private SQLiteDataAdapter da;

        //SQLite database connection string
        private String connString = SystemQueries.dbConnString();

        //SQLite transaction object
        private SQLiteTransaction transaction;

        //String array to hold old date.
        private String[] arrOldData = new String[4];

        //String to hold email of teacher to be notified of update.
        private String strTeacherEmail = "";

        //Background worker to handle task of initializing conference details.
        private System.ComponentModel.BackgroundWorker backgroundWorkerEmailUpdate = new System.ComponentModel.BackgroundWorker();
        private System.ComponentModel.BackgroundWorker backgroundWorkerEmailCancellation = new System.ComponentModel.BackgroundWorker();

        //String to hold whether counselor is needed.
        private Boolean counselorNeeded = false;

        //Array data field to hold the .TEXT properties for emailing updates.
        String[] arrNewData = new String[5];

        //Teacher name
        String teacherName = "";

        //Constructor.Querys database for future conferences and binds counselor combobox.
        public UpdateConference_Page()
        {
            InitializeComponent();

            //Connect to database.
            dbConn();

            //Fill datagrid.
            queryResults();

            //Bind counselor combobox.
            BindCounselorComboBox();

            //Set the background worker event handler for sending emails.
            this.backgroundWorkerEmailUpdate.DoWork += new System.ComponentModel.DoWorkEventHandler(this.backgroundWorkerEmailUpdate_DoWork);
            this.backgroundWorkerEmailCancellation.DoWork += new System.ComponentModel.DoWorkEventHandler(this.backgroundWorkerEmailCancellation_DoWork);
        }

        //Method to clear the textfields of new teacher form.
        private void ClearForm()
        {
            //Clear text fields.
            txtTeacherName.Clear();
            txtStudentName.Clear();
            counselorComboBox.Text = "";
            chkCounselor.IsChecked = false;
            txtDate.Text = "";
            txtTime.Clear();
            txtLocation.Clear();

            //Refocus to teacher first name textbox.
            txtTime.Focus();
        }

        //Method to ensure critical texboxes are not empty.
        private Boolean IsNotEmpty()
        {
            if (txtTeacherName.Text == String.Empty || txtStudentName.Text == String.Empty || txtDate.Text == String.Empty
                || txtTime.Text == String.Empty)
            {
                return false;
            }
            return true;
        } 

        //Method to hide all labels.
        public void HideAllLabels()
        {
            //Hide Labels.
            lblConferenceSuccess.Visibility = Visibility.Hidden;
            lblNoConferences.Visibility = Visibility.Hidden;
            lblConferenceCancelled.Visibility = Visibility.Hidden;
            lblInformationCheck.Visibility = Visibility.Hidden;
            lblValidTime.Visibility = Visibility.Hidden;
            lblTimeFrame.Visibility = Visibility.Hidden;
            lblValidDate.Visibility = Visibility.Hidden;
            lblNoConferenceSelected.Visibility = Visibility.Hidden;
            lblSQLiteError.Visibility = Visibility.Hidden;
        }

        //Hides the counselor combobox when unchecked
        private void chkCounselor_Unchecked(object sender, RoutedEventArgs e)
        {
            //Change counselor combobox and label visibility.
            lblCounselorName.Visibility = Visibility.Hidden;
            counselorComboBox.Visibility = Visibility.Hidden;
        }

        //Displays the counselor combobox when checked
        private void chkCounselor_Checked(object sender, RoutedEventArgs e)
        {
            //Change counselor combobox and label visiblity to visible.
            lblCounselorName.Visibility = Visibility.Visible;
            counselorComboBox.Visibility = Visibility.Visible;
        }

        /// <summary>
        /// Implements method from ScheduleReport to handle database connectivity.
        /// </summary>
        public void dbConn()
        {
            //Connect to database and open connection.
            conn = new SQLiteConnection(connString);
            conn.Open();
        }

        /// <summary>
        /// Implements method from ScheduleReport to query the desired table from 
        /// database.
        /// </summary>
        public void queryResults()
        {
            //Retrieve the today's query string from the SystemQueries class. 
            String sql = SystemQueries.futureConferencesQueryString();

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            da = new SQLiteDataAdapter(sql, connString);

            //Fill the dataset with the results. 
            da.Fill(ds);

            //If there are no records then prompt user that there are no scheduled
            //conferences for today. Otherwise just display records. 
            if (ds.Tables[0].Rows.Count == 0)
            {
                lblNoConferences.Visibility = Visibility.Visible;
            }
            else
            {
                lblNoConferences.Visibility = Visibility.Hidden;
            }

            //Format the date and times using dtFormat.
            dtFormat(ds);

            //Hide teacher id column from the datagrid.
            ds.Tables[0].Columns["Teacher ID"].ColumnMapping = MappingType.Hidden;
            ds.Tables[0].Columns["Teacher Email"].ColumnMapping = MappingType.Hidden;

            //Display results in datagrid view. 
            dataGridConference.ItemsSource = ds.Tables[0].DefaultView;

            //Close database connection.
            conn.Close();
            conn.Dispose();
        }

        //Method to format the date and time column of dataset.
        private void dtFormat(DataSet ds)
        {
            //Iterate through each row and format accordinly
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                //Create date time object
                DateTime dt = DateTime.Parse(row["Time"].ToString());
                //Set column equal to desired date pattern.
                row["Time"] = dt.ToString("hh:mm tt");
            }
        }

        //Method to query and bind the counselor combobox.
        private void BindCounselorComboBox()
        {
            //Connect to the database.
            dbConn();

            //Retrieve the teacher query string from the SystemQueries class and set the
            //teacher combobox contents to teacher names.
            String sql = SystemQueries.counselorComboBox();

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            SQLiteDataAdapter da2 = new SQLiteDataAdapter(sql, connString);

            //Fill the dataset with the results. 
            da2.Fill(ds);

            //Set combobox data source to query results.
            counselorComboBox.ItemsSource = ds.Tables[0].DefaultView;

            //Ensure the item source knows which member of the table to search.
            counselorComboBox.DisplayMemberPath = "Counselor";

            //Close connection.
            conn.Close();
            conn.Dispose();
        }

        //Method to set textboxes to values in data row view.
        private void setTextBoxes(DataRowView row)
        {
            //Create DateTime objects.
            DateTime dtDate = DateTime.Parse(row["Date"].ToString());
            DateTime dtTime = DateTime.Parse(row["Time"].ToString());

            //Format strings
            String strDate = dtDate.ToString("yyyy-MM-dd");
            String strTime = dtTime.ToString("HH:mm:ss");

            //Set text properties.
            txtStudentName.Text = row["Student"].ToString();
            txtTeacherName.Text = row["Teacher"].ToString();
            txtDate.Text = row["Date"].ToString();
            txtTime.Text = row["Time"].ToString();
            txtLocation.Text = row["Location"].ToString();

            //Set old data array and teacher email.
            arrOldData[0] = row["Teacher ID"].ToString();
            arrOldData[1] = row["Student ID"].ToString();
            arrOldData[2] = strDate + " " + strTime;
            arrOldData[3] = row["Location"].ToString();
            strTeacherEmail = row["Teacher Email"].ToString();

            //Check for whether counselor is needed.
            if (row["CN"].ToString().ToLower() == "yes")
            {
                //Set the check box to checked = true.
                chkCounselor.IsChecked = true;
                counselorComboBox.Text = row["Counselor"].ToString();
            }
            else
            {
                //Set the check box to checked = false.
                chkCounselor.IsChecked = false;
            }
        }

        //Box that handles setting controls on left to desired conference details.
        private void dataGridConference_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            //Create data grid view row object.
            DataRowView row = (DataRowView)dataGridConference.SelectedItems[0];

            //Retrieve column values and insert into textboxes.
            setTextBoxes(row);
        }

        //Method to update the conference.
        private void updateConferenceExecute()
        {
            //Open connection
            dbConn();

            //Set the SQLiteTransaction
            transaction = conn.BeginTransaction();

            //Create the SQLite command and set its properties
            SQLiteCommand command = new SQLiteCommand();
            command.Transaction = transaction;
            command.CommandText = this.updateConferenceString();
            command.ExecuteNonQuery();

            //Commit the transaction
            transaction.Commit();
            transaction.Dispose();

            //Display successful transaction message.
            lblConferenceSuccess.Visibility = Visibility.Visible;

            //Update datagridview.
            RefreshDataGridView();
        }

        //Method to update the conference.
        private void deleteConferenceExecute()
        {
            //Open connection
            dbConn();

            //Set the SQLiteTransaction
            transaction = conn.BeginTransaction();

            //Create the SQLite command and set its properties
            SQLiteCommand command = new SQLiteCommand();
            command.Transaction = transaction;
            command.CommandText = this.deleteConferenceString();
            command.ExecuteNonQuery();

            //Commit the transaction
            transaction.Commit();
            transaction.Dispose();

            //Display successful transaction message.
            lblConferenceCancelled.Visibility = Visibility.Visible;

            //Refresh datagridview.
            RefreshDataGridView();
        }

        //Check for scheduling conflict within the database and set the conflict
        //boolean datafield to true or false.
        public Boolean checkConflict()
        {
            //Format date and time.
            DateTime dtDate = DateTime.Parse(txtDate.Text);
            DateTime dtTime = DateTime.Parse(txtTime.Text);

            //String datafields to hold formatted date and time.
            String strDate = dtDate.ToString("yyyy-MM-dd");
            String strTime = dtTime.ToString("HH:mm:ss");

            //Connect to database
            dbConn();

            //Retrieve the teacher conflict query string from the SystemQueries class and find the 
            //count for particular teacher in conference table.
            String sql = SystemQueries.teacherConflictQueryString(Convert.ToInt32(arrOldData[0]),strDate,strTime);

            //SQLite command to retrieve count(*)
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            //Execute scalar to retrieve and set the value of counselor ID
            Object teacherCount = cmd.ExecuteScalar();

            //Convert to integer to retrieve count
            int count = Convert.ToInt32(teacherCount);
            
            //Close connection.
            conn.Close();

            //Return true or false based on the count
            if (count == 2)
            {
                return true;
            }
            
            return false;  
        }//End check conflict

        //Update conference string.
        private String updateConferenceString()
        {
            //Create DateTime objects.
            DateTime dtDate = DateTime.Parse(txtDate.Text);
            DateTime dtTime = DateTime.Parse(txtTime.Text);

            //Format strings
            String strDate = dtDate.ToString("yyyy-MM-dd");
            String strTime = dtTime.ToString("HH:mm:ss");
            
            //Check to see if counselor is needed.
            if (chkCounselor.IsChecked == true)
            {
                return @"UPDATE conferences " +
                        "SET date = datetime('" + strDate + " " + strTime + "'), counselorID = " + (counselorComboBox.SelectedIndex + 1) + 
                        ", location = '" + txtLocation.Text + "', counselorNeed = 'YES'" +
                         " WHERE teacherID = " + Convert.ToInt32(arrOldData[0]) + " AND studentID = " + Convert.ToInt32(arrOldData[1]) + 
                         " AND date = datetime('" + arrOldData[2] + "')";
            }

            //Return update string
            return @"UPDATE conferences " +
                        "SET date = datetime('" + strDate + " " + strTime + "')" + ", location = '" + txtLocation.Text + "', counselorID = 0, counselorNeed = 'NO'" +
                         " WHERE teacherID = " + Convert.ToInt32(arrOldData[0]) + " AND studentID = " + Convert.ToInt32(arrOldData[1]) +
                         " AND date = datetime('" + arrOldData[2] + "') ";
        }

        //Update conference string.
        private String deleteConferenceString()
        {
            return @"DELETE FROM conferences " +
                         "WHERE teacherID = " + Convert.ToInt32(arrOldData[0]) + " AND studentID = " + Convert.ToInt32(arrOldData[1]) +
                         " AND date = datetime('" + arrOldData[2] + "')";
        }

        //Method to handle emailing teacher of 
        //Method to refresh database with new results.
        private void RefreshDataGridView()
        {
            //Unregister selection changed event to fill data grid.
            dataGridConference.SelectionChanged -= dataGridConference_SelectionChanged;

            //Open connection and update dataview.
            dbConn();
            queryResults();

            //Register selection changed event to handle selection changes.
            dataGridConference.SelectionChanged += dataGridConference_SelectionChanged;
        }

        //Method to handle emailing teacher of changed conference.
        private void emailTeacherOfChange(String teacherName)
        {
            //Create email automation object.
            EmailAutomation email = new EmailAutomation();

            //Datafield to hold email body.
            String strBodyDetails = "";

            //Check if counselor is needed.
            if (counselorNeeded)
            {
                //Assign email body details.
                strBodyDetails = email.buildUpdateEmailDetailsString("YES", arrNewData[0], arrNewData[1], arrNewData[2], arrNewData[3],
                    arrOldData[1]);
            }
            else
            {
                //Assign email body details.
                strBodyDetails = email.buildUpdateEmailDetailsString("NO", arrNewData[0], arrNewData[1], arrNewData[2], arrNewData[3],
                    arrOldData[1]);
            }

            
            //Assign email body.
            email.buildUpdateEmailBody(teacherName, strBodyDetails);

            //Assign to address for teacher email.
            email.TO = strTeacherEmail;

            //Send the changed information email.
            email.sendUpdateEmail();

        }

        //Method to handle emailing teacher of changed conference.
        private void emailTeacherOfCancellation(String teacherName)
        {
            //Create email automation object.
            EmailAutomation email = new EmailAutomation();

            //Datafield to hold email body.
            String strBodyDetails = "";

                //Assign email body details.
                strBodyDetails = email.buildDeleteEmailDetailsString(arrNewData[0], arrNewData[1], arrNewData[2],
                        arrOldData[1]);

            //Assign email body.
            email.buildDeleteEmailBody(teacherName, strBodyDetails);

            //Assign to address for teacher email.
            email.TO = strTeacherEmail;
            
            //Send the changed information email.
            email.sendCancelledEmail();
        }

        private bool fieldsEmpty()
        {
            if (txtDate.Text == String.Empty || txtTime.Text == String.Empty)
                return true;
            return false;
        }
        private bool correctTimeFormat()
        {

            //Retrieve the parsed first integer to ensure it is 0
            String tmp2 = txtTime.Text.Substring(0, 1);

            //Check to ensure correct time format is entered.
            if (!(txtTime.Text.Contains(" AM")) && !(txtTime.Text.Contains(" PM")))
            {
                return false;
                Console.WriteLine("Contains AM OR PM");
            }
            if (!(txtTime.Text.EndsWith(" AM")) && !(txtTime.Text.EndsWith(" PM")))
            {
                return false;
                Console.WriteLine("Ends with AM OR PM");
            }
            if (!tmp2.Equals("0"))
            {
                return false;
                Console.WriteLine("Isnt 0");
            }

            return true;
        }

        /**private bool correctDateFormat()
        {
            //Check if string is right length
            if(txtDate.Text.Length != 9)
                return false;

            string tmp;
            string lbounds = "[^a-zA-Z]";
            string nbounds = @"[0-9]";
            //Regex.IsMatch(txtStudentFName.Text, @"^[a-zA-Z]+$")
            //Check to see if right format (##-mon-##)
            
            //check 1st two letters
            tmp = txtDate.Text.Substring(0,2);
            if (!Regex.IsMatch(tmp.ToString(), nbounds))
                return false;
            Console.WriteLine("Regex Pass");

            //Check '-' in the right place
            if (!(txtDate.Text.IndexOf("-") == 2) && !(txtDate.Text.LastIndexOf("-") == 6))
                return false;
            Console.WriteLine("Dashes Pass");

            //Ensure the month is correct
            tmp = txtDate.Text.Substring(3, 3);
            if (tmp.ToString() != "Jan" || tmp.ToString() != "Feb" || tmp.ToString() != "Mar" || tmp.ToString() != "Apr" || tmp.ToString() != "May" || tmp.ToString() != "Jun"
                || tmp.ToString() != "Jul" || tmp.ToString() != "Aug" || tmp.ToString() != "Sep" || tmp.ToString() != "Oct" || tmp.ToString() != "Nov" || tmp.ToString() != "Dec")
                return false;
            Console.WriteLine("Correct Month Pass");
            
            //Ensure the last two digits are numbers
            tmp = txtDate.Text.Substring(7, 2);
            Console.WriteLine(tmp);
            if (!Regex.IsMatch(tmp.ToString(), nbounds))
                return false;
            Console.WriteLine("Regex Pass");


            //Retrieve first digit & check to see if it is greater than 3
            tmp = txtDate.Text.Substring(0, 1);
            if (Convert.ToInt32(tmp) > 3) 
                return false;
            Console.WriteLine("<3");
           
            //Check for day correctness
            if (Convert.ToInt32(tmp) == 3)
            {
                Console.WriteLine("tmp = 3");
                //Feb - false
                if (txtDate.Text.Substring(3, 3).Equals("Feb"))
                    return false;

                //Get 2nd digit 
                tmp = txtDate.Text.Substring(1, 1);

                //Can't exceed 31
                if(Convert.ToInt32(tmp) > 1)
                    return false;
                Console.WriteLine("Not 31");
                
                //Apr, Jun, Sep, Nov - 30
                if(Convert.ToInt32(tmp) == 0)
                {
                    tmp = txtDate.Text.Substring(3,3);
                    if(tmp.ToString() != "Apr" || tmp.ToString() != "Jun" || tmp.ToString() != "Sept" || tmp.ToString() != "Nov")
                        return false;
                }
                Console.WriteLine("Not Apr, etc");

                //Jan, Mar, May, Jul, Aug, Oct, Dec - 31
                tmp = txtDate.Text.Substring(3,3);
                if (tmp.ToString() != "Jan" || tmp.ToString() != "Mar" || tmp.ToString() != "May" || tmp.ToString() != "Jul" 
                    || tmp.ToString() != "Aug" || tmp.ToString() != "Oct" || tmp.ToString() != "Dec")
                    return false;
                Console.WriteLine("Not Jan, etc");
            }//end check day if
            Console.WriteLine("Correct Date Format");
            return true;
        }**/

        //BackgroundWorker method that handles sending update emails to teachers.
        //Helps the U.I. become more responsive and not all processes occur on one thread.
        private void backgroundWorkerEmailUpdate_DoWork(object sender, DoWorkEventArgs e)
        {
            //Notify teacher of change.
            emailTeacherOfChange(teacherName);
        }//End Do Work event.

        //Method to handle the completion of backgroundworker async which updates teacher of new conference details.
        private void backgroundWorkerEmailUpdate_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //Don't do anything when process is completed.
        }

        //BackgroundWorker method that handles sending cancellation emails to teachers.
        //Helps the U.I. become more responsive and not all processes occur on one thread.
        private void backgroundWorkerEmailCancellation_DoWork(object sender, DoWorkEventArgs e)
        {
            //Notify teacher of cancellation.
            emailTeacherOfCancellation(teacherName);
        }//End Do Work event.

        //Method to handle the completion of backgroundworker async which informs teachers of cancellation.
        private void backgroundWorkerEmailCancellation_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //Don't do anything when process is completed.
        }  

        //Method to set new array data.
        private void setNewArrData()
        {
            //Insert new data into new data array.
            arrNewData[0] = txtDate.Text;
            arrNewData[1] = txtTime.Text;
            arrNewData[2] = txtLocation.Text;
            arrNewData[3] = counselorComboBox.Text;

            //Set teacher name
            teacherName = txtTeacherName.Text;
        }
        //Event handler to handle updating conference details and notifying teacher.
        private void btnUpdate_Click(object sender, RoutedEventArgs e)
        {
            //Hide all labels.
            HideAllLabels();
            try
            {
                //check to see if conference has been selected
                if (txtTeacherName.Text == String.Empty)
                {
                    throw new InvalidOperationException();
                }
                //Check if fields are empty
                if (fieldsEmpty())
                {
                    throw new MissingFieldException();
                }
                //Check if the Time is correctly formatted
                if (!correctTimeFormat())
                {
                    throw new FormatException();
                }

                DateTime dt = DateTime.Parse(txtTime.Text);
                String TIME24 = dt.ToString("HH:mm:ss");

                //Retrieve the parsed first two integers from the TIME24 string
                String tmp = TIME24.Substring(0, 2);
                int i = Convert.ToInt32(tmp);

                //Check if time is between bounds
                if (i < 14 || i > 18)
                {
                    throw new InvalidTimeZoneException();
                }

                //Check whether counselor is needed and update the datafield for background worker.
                if(chkCounselor.IsChecked == true)
                {
                    counselorNeeded = true;
                }else
                {
                    counselorNeeded = false;
                }

                //Set new data array.
                setNewArrData();

                //Execute update transactin for new conference details.
                updateConferenceExecute();

                //Execute the background worker for information teachers of updates.
                backgroundWorkerEmailUpdate.RunWorkerAsync();

                //Clear Form.
                ClearForm();
            }
            catch (Exception ex)
            {
                if (ex is InvalidOperationException)
                {
                    lblNoConferenceSelected.Visibility = Visibility.Visible;
                    conn.Close();
                }
                if (ex is ArgumentException)
                {
                    lblValidDate.Visibility = Visibility.Visible;
                    conn.Close();
                }
                if (ex is MissingFieldException)
                {
                    lblInformationCheck.Visibility = Visibility.Visible;
                    conn.Close();
                }
                if (ex is FormatException)
                {
                    lblValidTime.Visibility = Visibility.Visible;
                    conn.Close();
                }
                if (ex is InvalidTimeZoneException)
                {
                    lblTimeFrame.Visibility = Visibility.Visible;
                    conn.Close();
                }
                if (ex is SQLiteException)
                {
                    lblSQLiteError.Visibility = Visibility.Visible;
                    conn.Close();
                }
            }
        }

        //Event handler to handle deleting conference and notifying teacher.
        private void btnCancel_Click(object sender, RoutedEventArgs e)
        {
            //Test to ensure datagrid isn't empty and no textboxes are empty.
            if (dataGridConference.Items.Count > 0 && IsNotEmpty())
            {
                //Hide all labels.
                HideAllLabels();

                //Set new data array.
                setNewArrData();

                //Run background worker to inform teacher of cancellation.
                backgroundWorkerEmailCancellation.RunWorkerAsync();

                //Execute delete for cancellation of conference.
                deleteConferenceExecute();

                //Clear Form.
                ClearForm();
            }
        }

        //Event handler to ensure user is not able to schedule for past date.
        private void calendar_SelectedDateChanged(object sender, SelectionChangedEventArgs e)
        {
            //If the selected scheduling date is after current date, then display date in textbox.
            //Otherwise user attempted to select a date that has passed and therefore conference
            //cannot be scheduled on that day.
            if (calendar.SelectedDate >= System.DateTime.Today)
            {

                //Change textbox contents to the date selected from the calendar.
                txtDate.Text = ((DateTime)calendar.SelectedDate).ToString("D");
            }
            //If the date is not applicable, prompt user with message box.
            else
            {
                //Change textbox contents to empty.
                txtDate.Text = "";
            }
        }
    }
}
